/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package id3tagger.db;

import id3tagger.Song;
import id3tagger.SongDB;
import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import org.jaudiotagger.audio.AudioFileFilter;

/**
 *
 * @author monzie
 */
public class DBManager
{
    private Connection con;
    private Statement st;
    private ResultSet rs;

    public DBManager(String dbpath) throws SQLException, Exception
    {     
            Class.forName("org.sqlite.JDBC");
            con = DriverManager.getConnection("jdbc:sqlite:"+dbpath);//con sert à se connecter à la base
            //System.out.println("Connexion etablie");
            this.createDB();
    }

    public ArrayList <SongDB> getSongs(String query) throws SQLException
    {
        ArrayList <SongDB> songList = new ArrayList <SongDB>();
        
        this.executeQuery(query);

        if(!rs.isAfterLast()) rs.next();
        while(!rs.isAfterLast())
        {
            //System.out.println("Position du curseur : "+rs.getRow());
            //System.out.println("Row : "+rs.getString(3));
            SongDB tmp = new SongDB();
            tmp.setNmfic(rs.getString(1));
            tmp.setGenre(rs.getString(2));
            tmp.setArtiste(rs.getString(3));
            tmp.setAlbum(rs.getString(4));
            tmp.setTitre(rs.getString(5));
            //System.out.println(tmp+" "+tmp.getAlbum()+" "+tmp.getArtiste()+" "+tmp.getGenre());
            songList.add(tmp);
            rs.next();
        }

        return songList;
    }

    public void createDB() throws SQLException
    {
        this.execute("DROP TABLE IF EXISTS songs;");
        this.execute("CREATE TABLE songs (filename VARCHAR(256), genre VARCHAR(100), artiste VARCHAR(100), album VARCHAR(100), titre VARCHAR(100));");
    }

     /**
     * Cette méthode exécute une requète dont on attend le résultat
     * @param sqlquery
     * @throws SQLException
     */
    public void executeQuery(String sqlquery) throws SQLException
    {
        st = con.createStatement();
        rs = st.executeQuery(sqlquery);
    }

    /**
     * Cette méthode exécute une requète sans attendre de résultat
     * @param sqlquery
     * @throws SQLException
     */
    public void execute(String sqlquery) throws SQLException
    {
        //System.out.println("Requête SQL:"+sqlquery);
        st = con.createStatement();
        //System.out.println("Requête : "+sqlquery);
        st.execute(sqlquery);
    }

    public ResultSet getResultSet()
    {
        return rs;
    }

    public void addSong(Song s) throws Exception
    {
        if(s != null) execute(s.insertInto());
    }

    public ArrayList <String> listeColonne(String colonne, String where) throws SQLException, Exception
    {
        String query = QueryGenerator.list(colonne, where);
        this.executeQuery(query);

        ArrayList <String> toRet = new ArrayList <String> ();

        if(!rs.isAfterLast()) rs.next();
        while(!rs.isAfterLast())
        {
            toRet.add(rs.getString(1));
            //System.out.println(rs.getString(1));
            rs.next();
        }

        return toRet;
    }

    public void scanner(String root)
    {
        System.out.println("Scan de "+root);
        AudioFileFilter filtre= new AudioFileFilter();

        File f = new File(root);
        File[] listFiles = f.listFiles();
        for(int i = 0; i < listFiles.length; ++i)
        {
            if(listFiles[i].isDirectory())
            {
                scanner(listFiles[i].getAbsolutePath());                
            }

            else
            {
                if(filtre.accept(listFiles[i]))
                {
                    try
                    {
                        //System.out.println("Ajout de "+listFiles[i].getAbsolutePath());
                        this.addSong(new Song(listFiles[i]));
                    }
                    
                    catch (Exception ex)
                    {
                        System.err.println(ex);
                    }
                }
            }

            if(i == (listFiles.length - 1))
            {
                return;
            }
        }
    }
}